import xlwings as xw


def list_to_string(gr_list, g_col):
    for i in range(2, person_rows + 1):
        used_range = source_wb.sheets[gr_list].range(f"A{i}").expand("right").value
        # used_range = source_wb.sheets[grade_name].used_range.rows(i).end
        if type(used_range) == list:
            out_sheet.range(f"{g_col}{i}").value = ",".join(map(str, used_range[1:]))


def sort_commentotor(grade, commented, commentotor, num_row, personnum):
    grade_value = source_sheet.range(f"{grade}{num_row}").value
    commented_value = source_sheet.range(f"{commented}{num_row}").value
    commentotor_value = source_sheet.range(f"{commentotor}{num_row}").value

    for sheet in source_wb.sheet_names:
        if grade_value == sheet:
            break

    for commented_name in source_wb.sheets[sheet].range(f"A2:A{personnum}"):
        if commented_value == commented_name.value:
            commented_address = commented_name.get_address(True, False)
            address_col = commented_address.split("$")
            source_wb.sheets[sheet].range(f"B{address_col[1]}").insert(shift="right")
            source_wb.sheets[sheet].range(
                f"B{address_col[1]}"
            ).value = commentotor_value


def remove_duplicates(rows, i):
    col = None

    match i:
        case 1:
            col = input("请输入被评人所在列(英文所表示的列): ")
            source_wb.sheets[1].range("A1").value = source_sheet.range(f"{col}1").value
        case 2:
            col = input("请输入评价者级别所在列(英文所表示的列): ")
            source_wb.sheets[1].range("B1").value = source_sheet.range(f"{col}1").value

    duplicates_list = source_sheet.range(f"{col}2:{col}{rows}").value
    lst_no_duplicates = list(dict.fromkeys(duplicates_list))

    return lst_no_duplicates, col


if __name__ == "__main__":
    # 读取答卷数据(source)所在xlsx
    file_path_str = input("请将要处理的文件直接拖进来:")
    file_path_str = file_path_str.strip('"')
    source_wb = xw.Book(file_path_str)
    source_sheet = source_wb.sheets[0]
    source_sheet.activate()
    # 答卷最大行数
    row_max = source_sheet.used_range.rows.count
    # 新增一个sheet存放去重后的被评者与级别
    # 被评者
    no_duplicates_sheet = source_wb.sheets.add(name="deduplication", after=source_sheet)
    num = 1
    no_duplicates_list = remove_duplicates(row_max, num)
    noduplicates_row = no_duplicates_sheet.used_range.rows.count
    # 答卷中被评人列的英文
    commented_col = no_duplicates_list[1]
    # 把list转置为列
    no_duplicates_sheet.range("A2").options(transpose=True).value = no_duplicates_list[
        0
    ]
    person_rows = len(no_duplicates_list[0])
    num += 1
    # 级别
    no_duplicates_list = remove_duplicates(row_max, num)
    no_duplicates_sheet.range("B2").options(transpose=True).value = no_duplicates_list[
        0
    ]
    level_rows = len(no_duplicates_list[0])
    # 答卷中级别列的英文
    grade_col = no_duplicates_list[1]
    # 按级别来筛选被评者的评价者
    column_count = 1
    commentotor_column = input("请输入评价者所在列(英文所表示的列): ")

    while True:
        # 新增一个sheet并命名为某个级别
        grade_name = no_duplicates_sheet.range(f"B{column_count + 1}").value
        active_sheet = source_wb.sheets.add(
            name=grade_name,
            before=source_sheet,
        )
        active_sheet.range("A1").value = source_sheet.range(f"{commented_col}1").value
        # 把人名从去掉重复的中的表复制到每个级别的表A列
        no_duplicates_sheet.range(f"A2:A{person_rows+1}").copy(
            destination=active_sheet.range("A2")
        )
        active_sheet.range("B1").value = source_sheet.range(
            f"{commentotor_column}1"
        ).value
        column_count += 1
        if column_count == level_rows + 1:
            break

    grade_name = no_duplicates_sheet.range("B1").value

    person_rows += 1

    for row in range(2, row_max + 1):
        # 整理并输入被评者对应的评价者
        sort_commentotor(grade_col, commented_col, commentotor_column, row, person_rows)

    # 整理成标准导入格式
    out_sheet = source_wb.sheets.add(name="OUT", before=source_wb.sheets[0])
    no_duplicates_sheet.range(f"A2:A{person_rows+1}").copy(
        destination=out_sheet.range("A2")
    )
    out_sheet.range("A1").value = "被评者"
    out_sheet.range("B1").value = no_duplicates_list[0]
    for g_list in no_duplicates_list[0]:
        for gr_list in out_sheet.range((1, 2), (1, 2 + level_rows)):
            if g_list == gr_list.value:
                gra_col = gr_list.get_address(True, False).split("$")
                list_to_string(g_list, gra_col[0])
                break
    print("完成！")
